package model.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.ArrayList;

import model.bean.Account;
import model.bean.Members;
import model.common.Common;
import form.RegistrationForm;
import form.UpdateInfoForm;

/**
 * Class quản lý tài khoản thành viên
 * 
 * @author HoangDT
 * 
 */
public class AccountManagerDAO {

	/**
	 * Kiểm tra tài khoản đã tồn tại chưa
	 * 
	 * @param user
	 * @return
	 */
	public boolean Check(String user) {
		try {
			Common.connect();
			String sql = "select userName from tblUser where userName = '"
					+ user + "'";
			Statement st = Common.conn.createStatement();
			ResultSet rss = st.executeQuery(sql);
			return rss.next();
		} catch (SQLException ex) {
			System.out.println("Loi truy xuat CSDL!");
			ex.printStackTrace();
		} finally {
			if (Common.conn != null) {
				Common.disconnect();
			}
		}
		return false;
	}

	/**
	 * Kiểm tra user có quyền admin không
	 * 
	 * @param user
	 * @return
	 */
	public boolean CheckAdmin(String user) {
		int isAdmin = 0;
		try {
			Common.connect();
			String sql = "select groupId from tblAccount where userName = '"
					+ user + "'";
			Statement st = Common.conn.createStatement();
			ResultSet rss = st.executeQuery(sql);
			while (rss.next()) {
				isAdmin = rss.getInt("groupId");
			}
		} catch (SQLException ex) {
			System.out.println("Loi truy xuat CSDL!");
			ex.printStackTrace();
		} finally {
			if (Common.conn != null) {
				Common.disconnect();
			}
		}
		if (isAdmin == 1)
			return true;
		else
			return false;
	}

	/**
	 * Kiểm tra thông tin đăng nhập hợp lệ
	 * 
	 * @param user
	 * @param password
	 * @return
	 */
	public boolean CheckLogin(String user, String password) {
		try {
			Common.connect();
			String sql = "select * from tblAccount where userName = '" + user
					+ "' and password = '" + password + "' ";
			Statement st = Common.conn.createStatement();
			ResultSet rss = st.executeQuery(sql);
			return rss.next();
		} catch (SQLException ex) {
			System.out.println("Loi truy xuat CSDL!");
			ex.printStackTrace();
		} finally {
			if (Common.conn != null) {
				Common.disconnect();
			}
		}
		return false;
	}

	/**
	 * Đăng ký thông tin tài khoản
	 * 
	 * @param registrationForm
	 * @return
	 */
	public boolean Registration(RegistrationForm registrationForm) {
		try {
			Common.connect();
			String sql1 = "INSERT INTO tblAccount VALUES(?, ?, ?, ?)";
			String sql2 = "INSERT INTO tblUser(userName,firstName,lastName,gender,dOB,emailAddr,avatar,deleted) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";

			int check = 0;
			String firstName = registrationForm.getFirstName();
			String lastName = registrationForm.getLastName();
			String userName = registrationForm.getUserName();
			String password = registrationForm.getPassword();
			int gender = registrationForm.getGender();
			Time dOB = registrationForm.getDateOfBirth();
			String emailAddr = registrationForm.getEmailAddr();
			String avatar = registrationForm.getAvatar();
			int deleted = 0;

			PreparedStatement pst = Common.conn.prepareStatement(sql1);

			pst.setString(1, userName);
			pst.setString(2, password);
			pst.setInt(3, 0);// 0: thành viên; 1: quản lý
			pst.setInt(4, deleted);// 0: không xóa

			check = pst.executeUpdate();

			if (check != 1)
				return false;

			PreparedStatement pst2 = Common.conn.prepareStatement(sql2);
			pst2.setString(1, userName);
			pst2.setString(2, firstName);
			pst2.setString(3, lastName);
			pst2.setInt(4, gender);
			pst2.setTime(5, dOB);
			pst2.setString(6, emailAddr);
			pst2.setString(7, avatar);
			pst2.setInt(8, deleted);// Chưa xóa khỏi hệ thống

			check = pst2.executeUpdate();
			if (check == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			System.err
					.println("Da co loi xay ra. Them tai khoan khong thanh cong!!!");
			e.printStackTrace();
		} finally {
			Common.disconnect();
		}

		return false;
	}

	/**
	 * Lấy thông tin User
	 * 
	 * @param user
	 * @return
	 */
	public Account getInforUser(String user) {
		Account account = new Account();

		try {
			Common.connect();
			String sql = "SELECT * FROM tblAccount WHERE userName = ?";
			PreparedStatement statement = Common.conn.prepareStatement(sql);
			statement.setString(1, user);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				account.setUserName(rs.getString("userName"));
				account.setPassword(rs.getString("password"));
				account.setFirstName(rs.getString("firstName"));
				account.setLastName(rs.getString("lastName"));
				account.setGender(rs.getInt("gender"));
				account.setDOB(rs.getTime("DOB"));
				account.setEmailAddr(rs.getString("emailAddr"));
				account.setAvatar(rs.getString("avatar"));
				account.setDeleted(rs.getInt("deleted"));
			}
		} catch (SQLException ex) {
			System.out.println("Loi truy xuat CSDL!");
			ex.printStackTrace();
		} finally {
			if (Common.conn != null) {
				Common.disconnect();
			}
		}
		return account;
	}

	/**
	 * Cập nhật thông tin User
	 * 
	 * @param user
	 * @param password
	 * @param firstName
	 * @param lastName
	 * @return
	 */
	public boolean UpdateInfor(UpdateInfoForm form) {
		boolean result = false;
		String password = form.getPassWord();
		String userName = form.getUserName();
		String firstName = form.getFirstName();
		String lastName = form.getLastName();
		int gender = form.getGender();
		String avatar = form.getAvatar();
		Time dOB = form.getDateOfBirth();
		String emailAddr = form.getEmailAddr();
		if ("".equals(password)) {

			try {
				Common.connect();
				String sql = "UPDATE tblAccount SET password = ? WHERE userName = ?";
				PreparedStatement statement = Common.conn.prepareStatement(sql);
				statement.setString(1, password);
				statement.setString(2, userName);
				if (statement.executeUpdate() != 0)
					result = true;

			} catch (SQLException ex) {
				System.out.println("Loi truy xuat CSDL!");
				ex.printStackTrace();
			} finally {
				if (Common.conn != null) {
					Common.disconnect();
				}
			}
			result = false;
		} else if ("".equals(firstName)) {
			try {
				Common.connect();
				String sql = "UPDATE tblUser SET firstName = ? WHERE userName = ?";
				PreparedStatement statement = Common.conn.prepareStatement(sql);
				statement.setString(1, firstName);
				statement.setString(2, userName);
				if (statement.executeUpdate() != 0)
					result = true;

			} catch (SQLException ex) {
				System.out.println("Loi truy xuat CSDL!");
				ex.printStackTrace();
			} finally {
				if (Common.conn != null) {
					Common.disconnect();
				}
			}
			result = false;
		} else if ("".equals(lastName)) {
			try {
				Common.connect();
				String sql = "UPDATE tblUser SET lastName = ? WHERE userName = ?";
				PreparedStatement statement = Common.conn.prepareStatement(sql);
				statement.setString(1, lastName);
				statement.setString(2, userName);
				if (statement.executeUpdate() != 0)
					result = true;
			} catch (SQLException ex) {
				System.out.println("Loi truy xuat CSDL!");
				ex.printStackTrace();
			} finally {
				if (Common.conn != null) {
					Common.disconnect();
				}
			}
			result = false;
		} else if ("".equals(emailAddr)) {
			try {
				Common.connect();
				String sql = "UPDATE tblUser SET emailAddr = ? WHERE userName = ?";
				PreparedStatement statement = Common.conn.prepareStatement(sql);
				statement.setString(1, emailAddr);
				statement.setString(2, userName);
				if (statement.executeUpdate() != 0)
					result = true;
			} catch (SQLException ex) {
				System.out.println("Loi truy xuat CSDL!");
				ex.printStackTrace();
			} finally {
				if (Common.conn != null) {
					Common.disconnect();
				}
			}
			result = false;
		} else if ("".equals(avatar)) {
			try {
				Common.connect();
				String sql = "UPDATE tblUser SET avatar = ? WHERE userName = ?";
				PreparedStatement statement = Common.conn.prepareStatement(sql);
				statement.setString(1, avatar);
				statement.setString(2, userName);
				if (statement.executeUpdate() != 0)
					result = true;
			} catch (SQLException ex) {
				System.out.println("Loi truy xuat CSDL!");
				ex.printStackTrace();
			} finally {
				if (Common.conn != null) {
					Common.disconnect();
				}
			}
			result = false;
		}
		return result;
	}

	/**
	 * Method lấy về danh sách thành viên hiện có
	 * 
	 * @return
	 */
	public ArrayList<Members> GetListAccount() {
		Common.connect();
		ArrayList<Members> listMember = new ArrayList<Members>();

		try {
			String sql = "select userName,password from tblAccount ";

			Statement st = Common.conn.createStatement();
			ResultSet rss = st.executeQuery(sql);
			while (rss.next()) {
				Members account = new Members();
				String userName = rss.getString(1);
				String password = rss.getString(2);

				account.setUserName(userName);
				account.setPassword(password);

				listMember.add(account);
			}

		} catch (SQLException e) {
			System.err.println("loi thuc hien lay danh sách thành viên");
		} finally {
			Common.disconnect();
		}

		return listMember;
	}

	public void DeleteUser(String userName) {
		Common.connect();
		try {
			String sql = "UPDATE tblAccount SET deleted = 1 WHERE userName = ?";

			PreparedStatement statement = Common.conn.prepareStatement(sql);
			statement.setString(1, userName);
			statement.executeUpdate();
		} catch (SQLException ex) {
			System.out.println("Loi truy xuat CSDL!");
			ex.printStackTrace();
		} finally {
			if (Common.conn != null) {
				Common.disconnect();
			}
		}
	}
	public void ResetPassword(String userName, String password) {
		Common.connect();
		try {
			String sql = "UPDATE tblAccount SET password = ? WHERE userName = ?";

			PreparedStatement statement = Common.conn.prepareStatement(sql);
			statement.setString(1, password);
			statement.setString(2, userName);
			
			statement.executeUpdate();
		} catch (SQLException ex) {
			System.out.println("Loi truy xuat CSDL!");
			ex.printStackTrace();
		} finally {
			if (Common.conn != null) {
				Common.disconnect();
			}
		}
	}
}
